import akshare as ak
import datetime
import SimpleMySqlClass as smc


def get_hs300_stock_codes():
    """
    获取沪深300股票代码列表
    :return:
    """
    hs300 = ak.index_stock_cons_sina("000300")
    codeNames = hs300.loc[:, ["symbol", "code", "name"]]
    # codes = codes.tolist()
    return codeNames


# 获取数据库链接
def get_mysql_conn():
    conn = smc.SimpleMySqlClass("127.0.0.1", "stocks", "haojg", "haojg")
    return conn


def get_data_and_read():
    # 拉取远端数据
    stock_qfq_data = get_hs300_stock_codes()
    # 获取链接
    conn = get_mysql_conn()
    # 落库
    save_data_to_mysql(conn, stock_qfq_data)
    # 读数
    read_data_from_mysql(conn)
    # 关闭连接
    conn.close()


# 2, save data to mysql
def save_data_to_mysql(conn, stock_data_df):
    table = "t_hs_300"
    for index, row in stock_data_df.iterrows():
        # 插入新记录
        insert_data = {
            "id": 0,
            "symbol": row["symbol"],
            "code": row["code"],
            "name": row["name"],
            "create_time": datetime.datetime.now(),
            "update_time": datetime.datetime.now(),
        }
        # in_rs = conn.insert("t_stock_k_data", data)
        in_rs = conn.insertIgnore(table, insert_data)
        print("insert rs = ", in_rs)

        update_data = {
            "code": row["code"],
            "status": "Y",
            "update_time": datetime.datetime.now(),
        }
        where_condition = " code = '" + row["code"] + "'"
        up_rs = conn.update(table, update_data, where_condition)
        print("update rs = ", up_rs)

    print("-------------------------------")
    return


# 3, query data from mysql
def read_data_from_mysql(conn):
    tableName = "t_hs_300"
    # sql = f"select sd_date, sd_code, sd_open, sd_high, sd_low, sd_close, sd_volume, sd_amount from {tableName}"
    sql = f"select count(*) from {tableName}"
    rs = conn.get_one(sql)
    print("query rs = ", rs)
    pass


if __name__ == "__main__":

    # 获取沪深300代码列表
    get_data_and_read()

    print("end")
